# @Project: diablo
# @File: quality_init_dev
# @Time: 2023/12/13 16:36
# @User: Jolly
# @Email: jolly@007vin.com

"""
品质信息初始化 (测试环境)
数据来源为商品表
"""

import datetime
import time
import threading
import requests
from queue import Queue
from pypinyin import lazy_pinyin, Style

from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class QualityInit:

    def __init__(self):

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.154", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        self.hongde_group = ["19752aadc37746fda4023a2bfffa46c1",
                             "9fd96887306d4594ac85c9bb3404ae98",
                             "d7dc91c7d52d4cf79a2795385850babc",
                             "584a3ff537854102aff842c3e34daced",
                             "7813ff2f20d04bd58252b6c18735c392",
                             "e086c6ac452645c2822ad836ac28847f",
                             "da88bd366a6049f6b70c5f6395912396",
                             "0f60506053af4907b901b6d9ca132bb1",
                             "59e326e756a641c18c0d58d0df82030e",
                             "6d8dc5cc39ea431b81c88e53c213098b",
                             "23b63424bf3d4a87a51bac7049e7570c",
                             "9ba515ff52b84ef59370999f133246dc",
                             "9b726e87f1ea4255a1378d556cc9f5e2",
                             "f8d1ed3723214e9cb083f89cc4348903",
                             "3783bda80d78415cbed62fb88e1cab0d",
                             "5b612024616e47858bee94bc019aed7c",
                             "5303f5a79de84e7393e81c8778d69e0e",
                             "72a079ec9c4a415aab520d48d52bc1d0",
                             "b26d4b19a7004b0fac163409de616d93",
                             "bd86b3f4e2d640a8ba54d81adf50a502",
                             "6c124d09c73f4a0e91fb2ec947290cd8",
                             "83194859bf1948f3af2c4725bf311ed0",
                             "18967e237c6b400da8b8e2ccd4a2379d",
                             "c6184953d5d8406fa58d7c3c713a4ad2",
                             "ed2fbe6b0700468bb6c6f93cdf7fc5a8",
                             "91c80181631046be9bfcaec866a4e87a",
                             "b938852583494fada612a7bcb34e43a2",
                             "296e636203a841aa9246fbd630ab259f"]

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=350,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 300  # 最大连接数
        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.21',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@ll7GnJDopei',
        #     db='users',
        #     use_unicode=True)

        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.47',
            user='users',
            port=3306,
            passwd='uSR@#H^&JD',
            db='users',
            use_unicode=True)

        return pool

    def get_stock_related(self):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        hongde_group_str = ''
        for index, data in enumerate(self.hongde_group):

            if index == len(self.hongde_group) - 1:
                hongde_group_str = hongde_group_str + ", '" + str(data) + ""
                break
            else:
                hongde_group_str = hongde_group_str + ", '" + str(data) + "'"

        hongde_group_str = hongde_group_str[3:]

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and merchant_id in ('{hongde_group_str}') """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_cst_goods(self, cst_merchant_id):
        """
        根据厂商通id查找所有商品数据
        """

        # 测试环境
        url = "http://192.168.191.131:8900/v1/api/openapi/goods/xqp/alllist"
        # 正式环境
        # url = "http://192.168.191.245:9960/v1/api/openapi/goods/xqp/alllist"

        headers = {
            "Content-Type": "application/json"
        }

        params = {
            'yc_id': 'xqp0000000000000000001',
            'merchant_id': cst_merchant_id
        }
        response = requests.get(url=url, headers=headers, params=params)
        resp = response.json()

        if resp.get('code') != 1:
            print("厂商通id查找所有商品数据失败")

        return resp['data']

    def check_quality_exist(self, merchant_id, ori_quality):
        """
        确认品质是否存在
        """

        check = False

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select ori_quality, quality from diablo_quality_replace where merchant_id='{merchant_id}'
             and ori_quality='{ori_quality}' and status=1"""

            # id = AutoField()
            # status = SmallIntegerField(default=1, help_text='1. 正常使用. 0. 删除')
            # merchant_id = CharField(max_length=32, help_text='模式id', index=True)
            # app_key = CharField(max_length=32, help_text='app_key', index=True)
            # ori_quality = CharField(max_length=64, help_text='ERP原品质')
            # quality = CharField(max_length=64, help_text='替换品质')
            # quality_py = CharField(max_length=64, help_text='品质拼音')
            # create_time = DateTimeField(default=datetime.datetime.now)
            # update_time = DateTimeField(default=datetime.datetime.now)

            cursor.execute(sql)

            result = cursor.fetchall()
            # print(result, "---------", len(result))
            if len(result) > 0:

                check = True

                return check
                # for result_ in result:
                #     quality_list.append({
                #         "ori_quality": result_[0],
                #         "quality": result_[1]
                #     })
                #
                # quality_replace = {i.get('ori_quality', ''): i.get('quality', '') for i in quality_list}

        except Exception as e:
            print("获取品质信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return check

    def get_hongde_quality(self, ori_quality):
        """
        获取宏德原始品质
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select ori_quality, quality from diablo_quality_replace where merchant_id='3'
             and ori_quality='{ori_quality}' and status=1"""

            # id = AutoField()
            # status = SmallIntegerField(default=1, help_text='1. 正常使用. 0. 删除')
            # merchant_id = CharField(max_length=32, help_text='模式id', index=True)
            # app_key = CharField(max_length=32, help_text='app_key', index=True)
            # ori_quality = CharField(max_length=64, help_text='ERP原品质')
            # quality = CharField(max_length=64, help_text='替换品质')
            # quality_py = CharField(max_length=64, help_text='品质拼音')
            # create_time = DateTimeField(default=datetime.datetime.now)
            # update_time = DateTimeField(default=datetime.datetime.now)

            cursor.execute(sql)

            result = cursor.fetchall()
            # print(result, "---------", len(result))
            if len(result) > 0:

                quality = result[0][1]

                return quality

        except Exception as e:
            print("获取品质信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return ""

    def get_hongde_quality_zhibao(self, quality):
        """
        获取宏德原始品质
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select quality_name, quality_pinyin, quality_pinyin_initials, warranty, agreement_type,
             stocks_amount, stocks_total from diablo_quality_tags where merchant_id='3' and quality_name='{quality}'
             and status=1 and agreement_type='stocks'"""

            # id = AutoField(help_text='主键')
            # user_id = IntegerField(help_text='用户', default=0)
            # merchant_id = CharField(max_length=32, help_text='所属用户', index=True)
            # quality_name = CharField(max_length=32, verbose_name='名称', default='')
            # quality_pinyin = CharField(max_length=64, verbose_name='拼音', default='')
            # quality_pinyin_initials = CharField(max_length=64, verbose_name='拼音首字母', default='')
            # warranty = CharField(max_length=64, verbose_name='质保时间', default='')
            # status = IntegerField(default=1, help_text='状态')
            #
            # agreement_type = CharField(max_length=16, help_text='品质来源; stocks: 库存; warehouse: 订货舱',
            #                            default='stocks')
            # # 覆盖度相关
            # stocks_amount = IntegerField(default=0, help_text='品质覆盖商品数')
            # stocks_total = IntegerField(default=0, help_text='品质覆盖的商品库存数')
            #
            # class Meta:
            #     table_name = 'diablo_quality_tags'

            cursor.execute(sql)

            result = cursor.fetchall()
            # print(result, "---------", len(result))
            if len(result) > 0:

                zhibao = {
                        "user_id": 0,
                        "merchant_id": "3",
                        "quality_name": result[0][0],
                        "quality_pinyin": result[0][1],
                        "quality_pinyin_initials": result[0][2],
                        "warranty": result[0][3],
                        "status": 1,
                        "agreement_type": result[0][4],
                        "stocks_amount": result[0][5],
                        "stocks_total": result[0][6],
                    }

                return zhibao

        except Exception as e:
            print("获取品质质保信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return {}

    # 写入数据
    def write_data(self, *args):

        goods = args[0]
        stock_related = args[1]

        ori_quality = goods.get("brand_names")

        with lock:
            # 查找品质是否存在
            check = self.check_quality_exist(stock_related.get("merchant_id"), ori_quality)
            if not check:

                if stock_related.get("merchant_id") in self.hongde_group:
                    # 查找宏德品质映射关系，拼接写入
                    old_quality = self.get_hongde_quality(ori_quality)
                    if old_quality != "":
                        quality = old_quality
                    else:
                        quality = ori_quality
                else:
                    quality = ori_quality

                # 组装插入数据
                mysql_conn = self.mysql_conn.connection()

                try:
                    cursor = mysql_conn.cursor()

                    # id = AutoField()
                    # status = SmallIntegerField(default=1, help_text='1. 正常使用. 0. 删除')
                    # merchant_id = CharField(max_length=32, help_text='模式id', index=True)
                    # app_key = CharField(max_length=32, help_text='app_key', index=True)
                    # ori_quality = CharField(max_length=64, help_text='ERP原品质')
                    # quality = CharField(max_length=64, help_text='替换品质')
                    # quality_py = CharField(max_length=64, help_text='品质拼音')
                    # create_time = DateTimeField(default=datetime.datetime.now)
                    # update_time = DateTimeField(default=datetime.datetime.now)

                    quality_pinyin = ''.join(lazy_pinyin(quality))
                    quality_pinyin_initials = ''.join(lazy_pinyin(quality, style=Style.FIRST_LETTER))

                    key = ['status', 'merchant_id', 'app_key', 'ori_quality', 'quality',
                           'quality_py', 'create_time', 'update_time']

                    formatted_datetime = datetime.datetime.now().strftime('%Y-%m-%d') + " 00:00:00"

                    key_param = [1, stock_related.get("merchant_id"), stock_related.get("app_key"),
                                 ori_quality, quality, quality_pinyin_initials, formatted_datetime, formatted_datetime]

                    value = ""

                    for i, v in enumerate(key):
                        value += '"{}",'.format(key_param[i])

                    values = ", ".join('{0}=values({0})'.format(k) for k in key)

                    sql = '''insert into diablo_quality_replace ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                        ",".join(key), value[:-1], values)
                    print(sql)
                    cursor.execute(sql)
                    mysql_conn.commit()

                except Exception as e:
                    print("写入品质失败 - mysql：", e)
                finally:
                    mysql_conn.close()

                if stock_related.get("merchant_id") in self.hongde_group:
                    # 写入质保数据 --------------------------------------------------------
                    # 查找宏德对应质保
                    zhibao = self.get_hongde_quality_zhibao(quality)
                    if zhibao:

                        mysql_conn = self.mysql_conn.connection()

                        try:
                            cursor = mysql_conn.cursor()

                            # "user_id": 0,
                            # "merchant_id": "e36cf07027ec40aca7be752d45dc84d6",
                            # "quality_name": result_[0],
                            # "quality_pinyin": result_[1],
                            # "quality_pinyin_initials": result_[2],
                            # "warranty": result_[3],
                            # "status": 1,
                            # "agreement_type": result_[4],
                            # "stocks_amount": result_[5],
                            # "stocks_total": result_[6],

                            # quality_pinyin = ''.join(lazy_pinyin(quality))
                            quality_pinyin_initials = ''.join(lazy_pinyin(quality, style=Style.FIRST_LETTER))

                            key = ['user_id', 'merchant_id', 'quality_name', 'quality_pinyin', 'quality_pinyin_initials',
                                   'warranty', 'status', 'agreement_type', 'stocks_amount', 'stocks_total']

                            key_param = [zhibao.get('user_id'), stock_related.get("merchant_id"), zhibao.get('quality_name'),
                                         zhibao.get('quality_pinyin'), zhibao.get('quality_pinyin_initials'),
                                         zhibao.get('warranty'),
                                         zhibao.get('status'), zhibao.get('agreement_type'), zhibao.get('stocks_amount'),
                                         zhibao.get('stocks_total')]

                            value = ""

                            for i, v in enumerate(key):
                                value += '"{}",'.format(key_param[i])

                            values = ", ".join('{0}=values({0})'.format(k) for k in key)

                            sql = '''insert into diablo_quality_tags ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                                ",".join(key), value[:-1], values)
                            # print(sql)
                            cursor.execute(sql)
                            mysql_conn.commit()

                        except Exception as e:
                            print("写入品质质保信息失败 - mysql：", e)
                        finally:
                            mysql_conn.close()

                        return {}

    # 任务执行
    def task(self):
        q = Queue(maxsize=300)  # 设定最大队列数和线程数

        count = 1

        # 获取所有库存记录
        stock_related_list = self.get_stock_related()

        for stock_related in stock_related_list:

            goods_cst_id = stock_related.get("goods_cst_id")

            # 获取库存商品列表
            goods_list = self.get_cst_goods(goods_cst_id)

            for goods in goods_list:

                t = threading.Thread(target=self.write_data, args=(goods, stock_related))
                q.put(t)
                if q.full() == True:
                    thread_list = []
                    while q.empty() == False:
                        t = q.get()
                        thread_list.append(t)
                        t.start()
                    for t in thread_list:
                        t.join()

                print("当前写入数：", count,
                      ", 商家为：", stock_related.get("merchant_name"),
                      ", 品质为：", goods.get("brand_names"))

                count += 1

            # 处理残余队列数据
            thread_list = []
            while q.empty() == False:
                t = q.get()
                thread_list.append(t)
                t.start()
            for t in thread_list:
                t.join()


if __name__ == "__main__":
    QualityInit()
